﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH.App_Code
{
    public class MedForum
    {
        public string userName { get; set; }
        public string title { get; set; }
        public string post { get; set; }
        public int MedForumID { get; set; }
        public DateTime dateTime { get; set; }
        public string category { get; set; }
        public DateTime dateTimeUpdated { get; set; }
        public int noOfReply { get; set; }
        public string imagePath { get; set; }
        public string imageName { get; set; }
        public string country { get; set; }

        public static int NewThread(MedForum e)
        {
            SqlConnection conn = null;
            int newMedForumID = 0;

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "insert into MedicalForum(userName, title, post, dateTime, category, dateTimeUpdated, noOfReply, imagePath, imageName, country) VALUES (@userName, @title, @post, @dateTime, @category, @dateTimeUpdated, @noOfReply, @imagePath, @imageName, @country)";
                comm.Parameters.AddWithValue("@userName", e.userName);
                comm.Parameters.AddWithValue("@title", e.title);
                comm.Parameters.AddWithValue("@post", e.post);
                comm.Parameters.AddWithValue("@dateTime", e.dateTime);
                comm.Parameters.AddWithValue("@category", e.category);
                comm.Parameters.AddWithValue("@dateTimeUpdated", e.dateTimeUpdated);
                comm.Parameters.AddWithValue("@noOfReply", e.noOfReply);
                comm.Parameters.AddWithValue("@imagePath", e.imagePath);
                comm.Parameters.AddWithValue("@imageName", e.imageName);
                comm.Parameters.AddWithValue("@country", e.country);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as NewMedForumID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newMedForumID = int.Parse(dr["NewMedForumID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newMedForumID;
        }

        public static List<MedForum> GetMedForum()
        {
            SqlConnection conn = null;
            List<MedForum> result = new List<MedForum>();

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Select * from MedicalForum order by dateTimeUpdated desc";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MedForum md = new MedForum();
                    md.category = (string)dr["category"];
                    md.dateTime = (DateTime)dr["dateTime"];
                    md.post = (string)dr["post"];
                    md.title = (string)dr["title"];
                    md.userName = (string)dr["userName"];
                    md.dateTimeUpdated = (DateTime)dr["dateTimeUpdated"];
                    md.noOfReply = (int)dr["noOfReply"];
                    md.MedForumID = (int)dr["MF_ID"];
                    md.imagePath = (string)dr["imagePath"];
                    md.imageName = (string)dr["imageName"];
                    md.country = (string)dr["country"];
                    result.Add(md);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void updateMedReplyForum(int MF_ID)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Update MedicalForum set noOfReply = noOfReply + 1 where MF_ID = @MedForumID";
                comm.Parameters.AddWithValue("@MedForumID", MF_ID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void updateMedReplyForumDate(int MF_ID, DateTime dateTimeUpdated)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Update MedicalForum set dateTimeUpdated = @dateTimeUpdated where MF_ID = @MedForumID";
                comm.Parameters.AddWithValue("@MedForumID", MF_ID);
                comm.Parameters.AddWithValue("@dateTimeUpdated", dateTimeUpdated);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static List<MedForum> displayMedForum(int MedForumID)
        {
            SqlConnection conn = null;
            List<MedForum> result = new List<MedForum>();

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "Select * from MedicalForum where MF_ID = @MedForumID";
                comm.Parameters.AddWithValue("@MedForumID", MedForumID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    MedForum md = new MedForum();
                    md.category = (string)dr["category"];
                    md.dateTime = (DateTime)dr["dateTime"];
                    md.post = (string)dr["post"];
                    md.title = (string)dr["title"];
                    md.userName = (string)dr["userName"];
                    md.dateTimeUpdated = (DateTime)dr["dateTimeUpdated"];
                    md.noOfReply = (int)dr["noOfReply"];
                    md.MedForumID = (int)dr["MF_ID"];
                    md.imagePath = (string)dr["imagePath"];
                    md.imageName = (string)dr["imageName"];
                    result.Add(md);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void changeAvatars(string newImagePath, string newImageName, string userName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update MedicalForum set imagePath = @newImagePath, imageName = @newImageName where userName = @userName";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@newImagePath", newImagePath);
                comm.Parameters.AddWithValue("@newImageName", newImageName);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteMedForum(MedForum mf)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM MedicalForum WHERE MF_ID=@MedForumID";
                comm.Parameters.AddWithValue("@MedForumID", mf.MedForumID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}